<?php

namespace Wintel\ApiBundle\Model;

use Elasticsearch\Client;
use Symfony\Component\DependencyInjection\ContainerInterface;
use Icsoc\DataBundle\Model\BaseModel;

/**
 * Class ReportModel
 *
 * @package Wintel\ApiBundle\Model
 */
class ReportModel extends BaseModel
{
    /** @var \Doctrine\DBAL\Connection dbal */
    private $dbal;

    /** @var \Doctrine\DBAL\Connection dbal */
    private $cdrDbal;

    /**
     * 录音呼叫类型
     *
     * @var array
     */
    private $rcalltype = array(
        '1' => '呼出',
        '2' => '呼入',
    );

    /**
     * @param ContainerInterface $container
     */
    public function __construct(ContainerInterface $container)
    {
        $this->container = $container;
        $this->dbal = $this->container->get('doctrine.dbal.default_connection');
        $this->cdrDbal = $this->container->get('doctrine.dbal.cdr_connection');
    }

    /**
     * 呼入明细报表elasticsearch搜索从mongodb中取值
     *
     * @param array $param
     * @return array
     */
    public function getCallinListDataFromElasticsearch(array $param = array()) {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];

        $condition['term']['vcc_id'] = $vccId;
        $type = "win_incdr";
        $condition = array(
            'fixup' => array(
                'index' => 'incdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => 'id',
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => (int) $vccId)),
        );

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            }

            if (isset($info['filter']['ag_num']) && $info['filter']['ag_num'] != '-1') {
                $agId = (int) $info['filter']['ag_num'];
                $condition['term']['ag_id'] = array('type' => 'match', 'value' => $agId);
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $result = (int) $info['filter']['result'];
                $condition['term']['result'] = array('type' => 'match', 'value' => $result);
            }

            if (isset($info['filter']['caller']) && !empty($info['filter']['caller'])) {
                $condition['term']['caller'] = array('type' => 'wildcard', 'value' => $info['filter']['caller']);
            }

            if (isset($info['filter']['called']) && !empty($info['filter']['called'])) {
                $condition['term']['called'] = array('type' => 'wildcard', 'value' => $info['filter']['called']);
            }
            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $condition['term']['que_id'] = array('type' => 'match', 'value' => $info['filter']['que_id']);
            }
            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $condition['term']['group_id'] = array('type' => 'match', 'value' => $info['filter']['group_id']);
            }
            //中继号码
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $condition['term']['server_num'] = array('type' => 'match', 'value' => $info['filter']['server_num']);
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $info['filter']['ag_num'] == '-1') {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['que_id'] == '-1') {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $info['filter']['group_id'] == '-1') {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        $condition['export'] = empty($info['export']) ? 0 : 1;
        $rows = $this->getDataFromESAndMongo($condition, 'win_incdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        $gridData = array();
        foreach ($resultData as $k => $item) {
            $gridData[$k] = $item;
            $gridData[$k]['vcc_id'] = (string) $item['vcc_id'];
            $gridData[$k]['call_id'] = (string) $item['call_id'];
            $gridData[$k]['start_time'] = $item['start_time'] ? date("Y-m-d H:i:s", $item['start_time']) : "";
            $gridData[$k]['end_time'] = date("Y-m-d H:i:s", $item['end_time']);
            $gridData[$k]['quein_time'] = $item['quein_time'] ? date("Y-m-d H:i:s", $item['quein_time']) : "";
            $gridData[$k]['conn_time'] = $item['conn_time'] ? date("Y-m-d H:i:s", $item['conn_time']) : "";
        }
        unset($resultData);

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page,
            'total_pages' => $totalPages,
            'data' => $gridData,
        );
    }

    /**
     * 从elasticsearch+mongodb中获取呼出明细数据
     *
     * @param array $param
     * @return array
     */
    public function getCalloutListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);

        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];

        $type = "win_agcdr";
        $condition = array(
            'fixup' => array(
                'index' => 'agcdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => 'id',
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => (int) $vccId)),
        );
        $condition['term']['call_type'] = array('type' => 'should', 'value' => '1,3,5');

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            }

            if (isset($info['filter']['ag_num']) && $info['filter']['ag_num'] != '-1') {
                $agId = (int) $info['filter']['ag_num'];
                $condition['term']['ag_id'] = array('type' => 'match', 'value' => $agId);
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $result = (int) $info['filter']['result'];
                $condition['term']['result'] = array('type' => 'match', 'value' => $result);
            }

            if (isset($info['filter']['ag_phone']) && !empty($info['filter']['ag_phone'])) {
                $condition['term']['ag_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['ag_phone']);
            }

            if (isset($info['filter']['cus_phone']) && !empty($info['filter']['cus_phone'])) {
                $condition['term']['cus_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['cus_phone']);
            }

            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $condition['term']['que_id'] = array('type' => 'match', 'value' => $info['filter']['que_id']);
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $condition['term']['group_id'] = array('type' => 'match', 'value' => $info['filter']['group_id']);
            }

            if (isset($info['filter']['call_id'])) {
                $condition['term']['call_id'] = array('type' => 'match', 'value' => trim($info['filter']['call_id']));
            }

            if (isset($info['filter']['serv_num']) && !empty($info['filter']['serv_num'])) {
                $condition['term']['serv_num'] = array('type' => 'match', 'value' => trim($info['filter']['serv_num']));
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $info['filter']['ag_num'] == '-1') {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['que_id'] == '-1') {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $info['filter']['group_id'] == '-1') {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }

        $rows = $this->getDataFromESAndMongo($condition, 'win_agcdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        $gridData = array();
        foreach ($resultData as $k => $item) {
            $gridData[$k] = $item;
            $gridData[$k]['vcc_id'] = (string) $item['vcc_id'];
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $item['start_time']);
            $gridData[$k]['end_time'] = date("Y-m-d H:i:s", $item['end_time']);
        }
        unset($resultData);

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'total_pages' => $totalPages,
            'data' => $gridData,
            'page' => $page,
        );
    }

    /**
     * 从elassearch中获取坐席通话明细数据
     *
     * @param array $param
     * @return array
     */
    public function getCallListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];

        $type = "win_agcdr";
        $condition = array(
            'fixup' => array(
                'index' => 'agcdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => 'id',
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => (int) $vccId)),
        );

        if (isset($info['filter'])) {
            //开始时间
            if (!empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (!empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            }

            if (!empty($info['filter']['ag_num'])) {
                $condition['term']['ag_num'] = array('type' => 'match', 'value' => $info['filter']['ag_num']);
            }
            if (!empty($info['filter']['endresult'])) {
                $endResult = (int) $info['filter']['endresult'];
                if ($info['filter']['endresult'] == '11' || $info['filter']['endresult'] == '12') {
                    $condition['term']['endresult'] = array('type' => 'match', 'value' => $endResult);
                } else {
                    $condition['term']['endresult'] = array('type' => 'multiNomatch', 'value' => array('11', '12'), 'multi' => true);
                }
            }

            if (!empty($info['filter']['ag_phone'])) {
                $condition['term']['ag_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['ag_phone']);
            }

            if (!empty($info['filter']['cus_phone'])) {
                $condition['term']['cus_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['cus_phone']);
            }

            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $condition['term']['que_id'] = array('type' => 'match', 'value' => $info['filter']['que_id']);
            }

            if (isset($info['filter']['call_type']) && $info['filter']['call_type'] != '-1') {
                $condition['term']['call_type'] = array('type' => 'match', 'value' => $info['filter']['call_type']);
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $result = (int) $info['filter']['result'];
                $condition['term']['result'] = array('type' => 'match', 'value' => $result);
            }

            if (!empty($info['filter']['call_id'])) {
                $callId = (int) $info['filter']['call_id'];
                $condition['term']['call_id'] = array('type' => 'match', 'value' => $callId);
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $condition['term']['group_id'] = array('type' => 'match', 'value' => (int) $info['filter']['group_id']);
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $info['filter']['ag_num'] == '-1') {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['que_id'] == '-1') {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $info['filter']['group_id'] == '-1') {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }

        $rows = $this->getDataFromESAndMongo($condition, 'win_agcdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        /** @var array $callType 呼叫类型 */
        $callType = $this->container->getParameter('CALLTYPE');
        /** @var array $endReason 结束类型 */
        $endReason = $this->container->getParameter('ENDREASON');
        $gridData = array();
        foreach ($resultData as $k => $item) {
            $gridData[$k] = $item;
            $gridData[$k]['vcc_id'] = (string) $item['vcc_id'];
            $gridData[$k]['call_id'] = (string) $item['call_id'];
            $gridData[$k]['ag_phone'] = (string) $item['ag_phone'];
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $item['start_time']);
            $gridData[$k]['end_time'] = date("Y-m-d H:i:s", $item['end_time']);
            $gridData[$k]['call_type'] = isset($callType[$item['call_type']]) ? $callType[$item['call_type']] : '';
            $gridData[$k]['endresult'] = isset($endReason[$item['endresult']]) ? $endReason[$item['endresult']] : '其他';
        }
        unset($resultData);
        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'total_pages' => $totalPages,
            'data' => $gridData,
            'page' => $page
        );
    }

    /**
     * 坐席操作明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getAgentStateListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }
        $where = ' vcc_id = :vcc_id ';
        $condition['vcc_id'] = $vccId;

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $condition['vcc_id'] = -1;
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }
            if (isset($info['filter']['ag_num']) && !empty($info['filter']['ag_num'])) {
                $where .= " AND (ag_num LIKE '%".$info['filter']['ag_num']."%' OR ag_name LIKE '%".
                    $info['filter']['ag_num']."%')";
            }

            if (isset($info['filter']['ag_sta_type']) && $info['filter']['ag_sta_type'] != '-1') {
                $where .= " AND ag_sta_type = :ag_sta_type";
                $condition['ag_sta_type'] = $info['filter']['ag_sta_type'];
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 2);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        /** @var  $count (总记录数) */
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_agsta_detail '.
            'WHERE '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_agsta_detail', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_num,group_id,ag_name,ag_sta_type,ag_sta_reason,ag_login_ip,start_time,duration,bend".
                " FROM win_agsta_detail ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'].
                ' LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_num,group_id,ag_name,ag_sta_type,ag_sta_reason,ag_login_ip,start_time,duration,bend".
                " FROM win_agsta_detail ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            //获取所有自定义的操作
            $agStaReason = $this->getAgentStaReason($vccId);
            $keys = '';
            foreach($agStaReason as $key=>$val) {
                $keys .= $key.',';
            }
            $keys .= "0,1,11,''" ;
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_agsta_detail '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT ag_num,group_id,ag_name,ag_sta_type,if(if(ag_sta_type=1,'',ag_sta_reason)in(".$keys."),if(ag_sta_type=1,'',ag_sta_reason),-1)as ag_sta_reason,ag_login_ip,start_time,duration,bend,if(bend=1,(start_time+duration),'')as end_time".
                " FROM win_agsta_detail AS wad INNER JOIN(".
                " SELECT id from win_agsta_detail".
                " WHERE ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwad USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }
        $gridData = array();
        $agStaReason = $this->getAgentStaReason($vccId);
        /** @var array $staReason 操作原因 */
        $staReason = $this->container->getParameter('STAREASON');
        /** @var array $operationStaus 操作状态 */
        $operationStaus = $this->container->getParameter('OPERATIONSTATUS');

        foreach ($rows as $k => $v) {
            /** 计算结束时间 */
            if ($v['bend']) {
                $endTime = $v['start_time'] + $v['duration'];
            } else {
                $endTime = 0;
            }

            $gridData[$k] = $v;
            //$gridData[$k]['ag_num'] = $v['ag_num']." ".$v['ag_name'];
            $gridData[$k]['ag_login_ip'] = empty($v['ag_login_ip']) ? "静态坐席" : $v['ag_login_ip'];
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $v['start_time']);
            $gridData[$k]['end_time'] = empty($endTime) ? "" : date("Y-m-d H:i:s", $endTime);
            $gridData[$k]['duration'] = $this->container->get("icsoc_core.common.class")->formateTime($v['duration']);
            $gridData[$k]['bend'] = $v['bend'] ? '结束' : '';
            if ($v['ag_sta_type'] == 2) {
                if ($v['ag_sta_reason'] == 1) {
                    $gridData[$k]['ag_sta_reason'] = isset($staReason[$v['ag_sta_reason']]) ?
                        $staReason[$v['ag_sta_reason']] : '';
                } else {
                    $gridData[$k]['ag_sta_reason'] = isset($agStaReason[$v['ag_sta_reason']])
                        ? $agStaReason[$v['ag_sta_reason']] : '坐席主动示忙';
                }
            } else {
                $gridData[$k]['ag_sta_reason'] = '';
            }
            $gridData[$k]['ag_sta_type'] = isset($operationStaus[$v['ag_sta_type']]) ?
                $operationStaus[$v['ag_sta_type']] : "";
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }

    /**
     * 从elasticsearch中获取技能组通话明细数据
     *
     * @param array $param
     * @return array
     */
    public function getQueueListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = empty($param['big']) ? false : $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];
        $field = empty($info['sort']['field']) ? 'id' : $info['sort']['field'];
        $type = "win_queue_cdr";
        $condition = array(
            'fixup' => array(
                'index' => 'queue_cdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => $field,
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => (int) $vccId)),
        );
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $condition['term']['vcc_id'] = array('type' => 'match', 'value' => -1);
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'ent_que_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'ent_que_time', 'operation' => 'lte');
            }
            if (isset($info['filter']['ag_num']) && $info['filter']['ag_num'] != '-1') {
                $agId = (int) $info['filter']['ag_num'];
                $condition['term']['ag_id'] = array('type' => 'match', 'value' => $agId);
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $condition['term']['endresult'] = array('type' => 'match', 'value' => $info['filter']['result']);
            } else {
                $condition['term']['endresult'] = array('type' => 'nomatch', 'value' => 21);//过滤掉21;
            }

            //坐席号码
            if (isset($info['filter']['called']) && !empty($info['filter']['called'])) {
                $condition['term']['ag_phone'] = array('type' => 'wildcard', 'value' => $info['filter']['called']);
            }
            if (isset($info['filter']['que_id']) && $info['filter']['que_id'] != '-1') {
                $condition['term']['que_id'] = array('type' => 'match', 'value' => $info['filter']['que_id']);
            }
            //中继号码
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $condition['term']['server_num'] = array('type' => 'match', 'value' => $info['filter']['server_num']);
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $info['filter']['ag_num'] == '-1') {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['que_id'] == '-1') {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && !isset($info['filter']['group_id'])) {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        //如果是异步导出
        if ($big == true) {
            $esSearch['code'] = 200;
            $esSearch['data'] = $this->container->get('icsoc_data.model.record')->getEsSearch($condition);

            return $esSearch;
        }
        $condition['export'] = empty($info['export']) ? 0 : 1;
        $rows = $this->getDataFromESAndMongo($condition, 'win_queue_cdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        $gridData = array();
        $endResults = $this->container->getParameter('END RESULT');
        foreach ($resultData as $k => $item) {
            $gridData[$k] = $item;
            $gridData[$k]['ent_que_time'] = date('Y-m-d H:i:s', $item['ent_que_time']);
            $gridData[$k]['end_time'] = date('Y-m-d H:i:s', $item['end_time']);
            $gridData[$k]['link_time'] = empty($item['link_time']) ? '未接通' : date('Y-m-d H:i:s', $item['link_time']);
            $gridData[$k]['assign_time'] = empty($item['assign_time']) ? '未分配' : date('Y-m-d H:i:s', $item['assign_time']);
            $gridData[$k]['endresult'] = (!array_key_exists($item['endresult'], $endResults)) ? '未接通' :
                $endResults[$item['endresult']];
        }
        unset($resultData);

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page,
            'total_pages' => $totalPages,
            'data' => $gridData,
        );
    }

    /**
     * 获取漏话sql
     *
     * @param  array $param
     *
     * @return string $sql
     */
    public function getLostListSql(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);

        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = " vcc_id = $vccId ";
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $startDate = strtotime($startDate);
                $where .= "AND start_time >= $startDate ";
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $endDate = strtotime($endDate);
                $where .= "AND start_time <= $endDate ";
            }

            if (isset($info['filter']['caller']) && !empty($info['filter']['caller'])) {
                $where .= " AND caller LIKE '%".$info['filter']['caller']."%'";
            }
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $where .= " AND server_num LIKE '%".$info['filter']['server_num']."%'";
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }

        $count = $this->dbal->fetchColumn('SELECT count(*) '.
            'FROM win_lost_cdr '.
            'WHERE '.$where);
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_lost_cdr', $info);

        $sql = "SELECT caller,caller_areacode,caller_areaname,que_name,server_num,start_time,reason ".
            "FROM win_lost_cdr ".
            "WHERE ".$where.
            ' ORDER BY '.$page['sort'];

        return $sql;
    }

    /**
     * (mongodb)漏话明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getLostListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($info['pagination']['rows']) ? 10 : $info['pagination']['rows'];
        $page = empty($info['pagination']['page']) ? 1 : $info['pagination']['page'];
        $order = empty($info['sort']['order']) ? 'desc' : $info['sort']['order'];
        //$field = empty($info['sort']['field']) ? 'id' : $info['sort']['field'];

        $condition['term']['vcc_id'] = $vccId;
        $type = "win_incdr";
        $condition = array(
            'fixup' => array(
                'index' => 'incdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => 'id',
            ),
            'term' => array(
                'vcc_id' => array('type' => 'match', 'value' => (int) $vccId),
                'result' => array('type' => 'nomatch', 'value' => (int) 0),
            ),
        );

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate), 'field' => 'start_time', 'operation' => 'lte');
            }
            if (isset($info['filter']['caller']) && !empty($info['filter']['caller'])) {
                $condition['term']['caller'] = array('type' => 'wildcard', 'value' => $info['filter']['caller']);
            }
            //中继号码
            if (isset($info['filter']['server_num']) && !empty($info['filter']['server_num'])) {
                $condition['term']['server_num'] = array('type' => 'match', 'value' => $info['filter']['server_num']);
            }
            if (isset($info['filter']['reason']) && $info['filter']['reason'] != -1) {
                $condition['term']['result'] = array('type' => 'match', 'value' => (int) $info['filter']['reason']);
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id'])) {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id'])) {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id'])) {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        $condition['export'] = empty($info['export']) ? 0 : 1;
        //如果是异步导出
        if ($big == true) {
            $esSearch['code'] = 200;
            $esSearch['data'] = $this->container->get('icsoc_data.model.record')->getEsSearch($condition);

            return $esSearch;
        }
        $rows = $this->getDataFromESAndMongo($condition, 'win_incdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];
        $gridData = array();
        /** @var array $lostReason 漏话原因 */
        $lostReason = $this->container->getParameter('INRESULT');
        foreach ($resultData as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = date("Y-m-d H:i:s", $v['start_time']);
            $gridData[$k]['result'] = isset($lostReason[$v['result']]) ? $lostReason[$v['result']] : '';
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page,
            'total_pages' => $totalPages,
            'data' => $gridData,
        );
    }

    /**
     * 咨询三方数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getConferenceListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = ' vcc_id = :vcc_id';
        $condition['vcc_id'] = $vccId;
        $where .= ' AND ext_type = 1 ';
        $time = $this->container->get('icsoc_core.common.class')->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $where .= ' AND id = -1 ';
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }
            if (isset($info['filter']['ag_ernum']) && !empty($info['filter']['ag_ernum'])) {
                $where .= " AND (ag_ernum LIKE '%".$info['filter']['ag_ernum']."%' OR ".
                    "ag_ername LIKE '%".$info['filter']['ag_ernum']."%')";
            }

            if (isset($info['filter']['endresult']) && $info['filter']['endresult'] != '-1') {
                $where .= " AND endresult = :endresult";
                $condition['endresult'] = $info['filter']['endresult'];
            }

            if (isset($info['filter']['call_phone']) && !empty($info['filter']['call_phone'])) {
                $where .= " AND call_phone LIKE '%".$info['filter']['call_phone']."%'";
            }

            if (isset($info['filter']['ext_phone']) && !empty($info['filter']['ext_phone'])) {
                $where .= " AND ext_phone LIKE '%".$info['filter']['ext_phone']."%'";
            }

            if (isset($info['filter']['max_secs']) && !empty($info['filter']['max_secs'])) {
                $where .= " AND conn_secs >='".$info['filter']['max_secs']."'";
            }

            if (isset($info['filter']['min_secs']) && !empty($info['filter']['min_secs'])) {
                $where .= " AND conn_secs <='".$info['filter']['min_secs']."'";
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_er', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        /** @var  $count (总记录数) */
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_extcdr '.
            'WHERE '.$where,
            $condition
        );
        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_extcdr', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,conn_time,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'].
                ' LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,conn_time,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_extcdr '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,conn_time,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr AS wq INNER JOIN(".
                " SELECT id from win_extcdr ".
                " WHERE ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwq USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }

        $gridData = array();
        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : "";
            $gridData[$k]['conn2_time'] = $v['conn2_time'] ? date("Y-m-d H:i:s", $v['conn2_time']) : "";
            $gridData[$k]['conn_time'] = $v['conn_time'] ? date("Y-m-d H:i:s", $v['conn_time']) : "";
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }

    /**
     * 监听强插数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getMonitorListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $big = $param['big'];
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $where = ' vcc_id = :vcc_id';
        $condition['vcc_id'] = $vccId;
        $where .= ' AND ext_type = 2 ';

        $time = $this->container->get('icsoc_core.common.class')->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $where .= ' AND id = -1 ';
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }
            if (isset($info['filter']['ag_ernum']) && !empty($info['filter']['ag_ernum'])) {
                $where .= " AND (ag_ernum LIKE '%".$info['filter']['ag_ernum']."%' OR ".
                    "ag_ername LIKE '%".$info['filter']['ag_ernum']."%')";
            }

            if (isset($info['filter']['endresult']) && $info['filter']['endresult'] != '-1') {
                $where .= " AND endresult = :endresult";
                $condition['endresult'] = $info['filter']['endresult'];
            }

            if (isset($info['filter']['call_phone']) && !empty($info['filter']['call_phone'])) {
                $where .= " AND call_phone LIKE '%".$info['filter']['call_phone']."%'";
            }

            if (isset($info['filter']['ext_phone']) && !empty($info['filter']['ext_phone'])) {
                $where .= " AND ext_phone LIKE '%".$info['filter']['ext_phone']."%'";
            }

            if (isset($info['filter']['max_secs']) && !empty($info['filter']['max_secs'])) {
                $where .= " AND conn_secs >='".$info['filter']['max_secs']."'";
            }

            if (isset($info['filter']['min_secs']) && !empty($info['filter']['min_secs'])) {
                $where .= " AND conn_secs <='".$info['filter']['min_secs']."'";
            }

            if (isset($info['filter']['group_id']) && $info['filter']['group_id'] != '-1') {
                $where .= " AND group_id = :group_id ";
                $condition['group_id'] = $info['filter']['group_id'];
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('ag_er', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        /** @var  $count (总记录数) */
        $count = $this->cdrDbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_extcdr '.
            'WHERE '.$where,
            $condition
        );

        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_extcdr', $info);
        /** 如果是导出不分页获取数据 */
        if (empty($info['export'])) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'].
                ' LIMIT '.$page['start'].','.$page['limit'],
                $condition
            );
        } else if(!empty($info['export']) && $big == false) {
            $rows = $this->cdrDbal->fetchAll(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr ".
                "WHERE ".$where.
                ' ORDER BY '.$page['sort'],
                $condition
            );
        } else {
            /** 最大的ID **/
            $maxId = $this->dbal->fetchColumn(
                'SELECT MAX(id) '.
                'FROM win_extcdr '
            );
            $rows = $this->container->get('icsoc_data.model.export')->interpolateQuery(
                "SELECT ag_ernum,group_id,call_phone,que_name,ag_ednum,ext_phone,start_time,conn2_time,conn1_secs,conn2_secs,".
                "conn_secs,endresult,ag_ername,ag_edname ".
                "FROM win_extcdr AS wq INNER JOIN(".
                " SELECT id from win_extcdr".
                " WHERE ".$where." AND id <= '".$maxId."'".
                ' ORDER BY '.$page['sort'].' %LIMIT%) AS wwq USING (id)',
                $condition
            );

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $count,
                'sql' => $rows,
            );
        }
        $gridData = array();
        foreach ($rows as $k => $v) {
            $gridData[$k] = $v;
            $gridData[$k]['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : "";
            $gridData[$k]['conn2_time'] = $v['conn2_time'] ? date("Y-m-d H:i:s", $v['conn2_time']) : "";
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'page' => $page['page'],
            'total_pages' => $page['totalPage'],
            'data' => $gridData,
        );
    }

    /**
     * 技能组来电分配数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getInallotListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }
        $where = 'vcc_id = :vcc_id AND que_id != 0 ';
        $condition = array('vcc_id' => $vccId);

        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $condition['vcc_id'] = -1;
        }
        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time >= :start_date ";
                $condition['start_date'] = strtotime($startDate);
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $where .= "AND start_time <= :end_date ";
                $condition['end_date'] = strtotime($endDate);
            }

            if (isset($info['filter']['result']) && $info['filter']['result'] != '-1') {
                $where .= " AND result = :result";
                $condition['result'] = $info['filter']['result'];
            }
        }

        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityCondition('', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 406,
                'message' => '权限内无任何数据',
            );
        } else {
            $where .= $authority;
        }
        /** @var  $count (总记录数) */
        $count = $this->dbal->fetchColumn(
            'SELECT count(*) '.
            'FROM win_incdr '.
            'WHERE '.$where,
            $condition
        );

        $page = $this->container->get("icsoc_data.helper")->getPageInfoExt($count, 'win_incdr', $info);
        $rows = $this->cdrDbal->fetchAll(
            "SELECT que_name,count(*) AS num ".
            "FROM win_incdr ".
            "WHERE ".$where.
            " GROUP BY que_id".
            ' ORDER BY '.$page['sort'],
            $condition
        );

        $data = array();

        foreach ($rows as $k => $v) {
            $data[$k]['que_name'] = $v['que_name'];
            $data[$k]['num'] = $v['num'];
            $data[$k]['rate'] = empty($count) ? '0.00%' : round($v['num'] / $count * 100, 2).'%';
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => count($data),
            'data' => $data,
        );
    }

    /**
     * 满意度评价汇总数据从mongodb获取数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getEvaluateCollectListDataFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $condition = array('vcc_id' => (int)$vccId);

        $esCondition = array(
            'fixup' => array(
                'index' => 'agcdr',
                'type' => "win_agcdr",
            ),
            'term' => array(
                'vcc_id' => array('type' => 'match', 'value' => (int) $vccId),
                'result' => array('type' => 'match', 'value' => (int) 0)
            ),
        );
        $time = $this->container->get("icsoc_core.common.class")->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');
        if ($time == 'no') {
            $condition['term']['vcc_id'] = array('type' => 'match', 'value' => 0);
        }

        if (isset($info['filter'])) {
            //开始时间
            if (isset($info['filter']['start_time']) && !empty($info['filter']['start_time'])) {
                $startDate = $info['filter']['start_time'];
                if (isset($time['startTime'])) {
                    $startDate = $time['startTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDate('开始日期不正确', $startDate, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['date'] = array('$gte' => $startDate);
                $esCondition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startDate), 'field' => 'start_time', 'operation' => 'gte');
            }
            //结束时间
            if (isset($info['filter']['end_time']) && !empty($info['filter']['end_time'])) {
                $endDate = $info['filter']['end_time'];
                if (isset($time['endTime'])) {
                    $endDate = $time['endTime'];
                }
                $msg = $this->container->get('icsoc_data.helper')->isDate('结束日期不正确', $endDate, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }
                $condition['date'] = array_merge($condition['date'], array('$lte' => $endDate));
                $esCondition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endDate) + 86399, 'field' => 'start_time', 'operation' => 'lte');
            }
            //坐席名称
            if (isset($info['filter']['ag_id']) && $info['filter']['ag_id'] != -1) {
                $condition = array_merge($condition, array('ag_id' => (int)$info['filter']['ag_id']));
                $esCondition['term']['ag_id'] = array('type' => 'match', 'value' => (int) $info['filter']['ag_id']);
            }
        }
        /** @var  $authority (获取数据权限) */
        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            $agId = array(-1);
            if (!empty($authority['ag_id']) && $info['filter']['ag_id'] == '-1') {
                $agId = $authority['ag_id'];
                $condition['ag_id'] = array('$in' => $agId);
                $esCondition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $info['filter']['ag_id'] == '-1') {
                //新的满意度汇总报表只有ag_id的搜索，所以要把技能组权限改成坐席的权限来控制
                $tmpRes = $this->dbal->fetchAll("select ag_id from win_agqu where que_id in (".implode(',', $authority['que_id']).")");
                $tmpIds = array();
                foreach ($tmpRes as $k => $v) {
                    $tmpIds[] = $v['ag_id'];
                }
                $agId = array_merge($agId, $tmpIds);
                $condition['ag_id'] = array('$in' => $agId);
                $esCondition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $info['filter']['ag_id'] == '-1') {
                $tmpRes = $this->dbal->fetchAll("select id from win_agent where group_id in (".implode(',', $authority['group_id']).") and is_del = 0");
                $tmpGroupIds = array();
                foreach ($tmpRes as $k => $v) {
                    $tmpGroupIds[] = $v['id'];
                }
                $agId = array_merge($agId, $tmpGroupIds);
                $condition['ag_id'] = array('$in' => $agId);
                $esCondition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        //从mongodb中，通过条件获取数据的值
        $document = $this->container->get('icsoc_core.mongodb_common.class')->otherGetDataForPage($condition, 'evalute');
        //默认值
        $hand['ag_num'] = '';
        $hand['evalute1'] = 0;
        $hand['evalute-1'] = 0;
        $hand['evalute-3'] = 0;
        $hand['evalute-4'] = 0;
        $hand['defeat'] = 0;
        $hand['ag_id'] = 0;
        $hand['transfer_rate'] = '0%';
        $footer['ag_num'] = '合计';
        $footer['evalute1'] = 0;
        $footer['evalute-1'] = 0;
        $footer['evalute-3'] = 0;
        $footer['evalute-4'] = 0;
        $footer['defeat'] = 0;
        $footer['ag_id'] = 0;
        //查询自定义的满意度 定义所有字段的默认值
        $evaluates = $this->container->get('icsoc_data.model.evaluate')->getEvaluateConfig(array('vccId' => $vccId));
        $otherData = json_decode($evaluates, true);
        foreach ($otherData as $key => $val) {
            foreach ($val['keys'] as $ke => $va) {
                $hand = array_merge($hand, array($va['id'] => 0));
                $footer = array_merge($footer, array($va['id'] => 0));
                if (!empty($va['child']['keys']) && is_array($va['child']['keys'])) {
                    foreach ($va['child']['keys'] as $k => $v) {
                        $hand = array_merge($hand, array($v['id'] => 0));
                        $footer = array_merge($footer, array($v['id'] => 0));
                    }
                }
            }
        }
        $result = array();
        $footerData = $footer;

        $agData = $this->container->get('doctrine.dbal.default_connection')->fetchAll(
            "SELECT id,ag_name,ag_num FROM win_agent WHERE is_del=0 AND vcc_id=:vcc_id",
            array(':vcc_id' => $vccId)
        );

        foreach($document as $restaurant) {
            $restaurant = get_object_vars($restaurant);
            $haveing = false;
            $haveAgent = false;
            foreach ($agData as $val) {
                if ($restaurant['ag_id'] == $val['id']) {
                    $restaurant['ag_name'] = $val['ag_name'];
                    $restaurant['ag_num'] = $val['ag_num'];
                    $haveAgent = true;
                }
            }
            //没有坐席时跳出循环
            if (!$haveAgent) {
                continue;
            }
            /* 未评论 */
            $restaurant['defeat'] = (empty($restaurant['evalute-1']) ? 0 : $restaurant['evalute-1'])
                + (empty($restaurant['evalute-3']) ? 0 : $restaurant['evalute-3'])
                + (empty($restaurant['evalute-4']) ? 0 : $restaurant['evalute-4']);
            foreach ($footer as $a => $b) {
                if ($a != '_id' && $a != 'ag_id' && $a != 'date' && $a != 'vcc_id' && $a != 'ag_name' && $a != 'ag_num') {
                    if (isset($footerData[$a])) {
                        $footerData[$a] += empty($restaurant[$a]) ? '' : $restaurant[$a];
                    } else {
                        $footerData[$a] = empty($restaurant[$a]) ? $b : $restaurant[$a];
                    }
                }
            }
            if (!empty($result)) {
                foreach ($result as $k => $v) {
                    if ($v['ag_id'] == $restaurant['ag_id']) {
                        $haveing = true;
                        $sumArr = (array_merge_recursive($v,$restaurant));
                        foreach ($sumArr as $ke => $vl) {
                            if ($ke != '_id' && $ke != 'ag_id' && $ke != 'date' && $ke != 'vcc_id' && $ke != 'ag_name' && $ke != 'ag_num') {
                                if (is_array($vl)) {
                                    $result[$k][$ke] = $vl[0] + $vl[1];
                                } else {
                                    $result[$k][$ke] = $sumArr[$ke];
                                }
                            }
                        }
                    }
                }
            }
            if (!$haveing) {
                foreach ($hand as $k => $v) {
                    if (empty($restaurant[$k])) {
                        $restaurant[$k] = $v;
                    }
                }
                $result[] = $restaurant;
            }
        };
        unset($document);
        /* 参评率 转接率*/
        $counts = 0;
        foreach ($result as $keys => $vals) {
            $esCondition['term']['ag_id'] = array('type' => 'match', 'value' => (int)$vals['ag_id']);
            $arrCount = $this->container->get('icsoc_data.model.record')->getEsCount($esCondition);
            $count = $arrCount['count'];
            $counts += $count;
            $result[$keys]['evalute_rate'] = empty($vals['evalute1'] + $vals['evalute-3']) ? '0%' : (round($vals['evalute1']/($vals['evalute1'] + $vals['evalute-3']), 4)*100).'%';
            $transferRate = empty($count) ? 0 : (round(($vals['evalute1'] + $vals['evalute-3'])/$count, 4)*100);
            if ($transferRate > 100) {
                $result[$keys]['transfer_rate'] = '100%';
            } else {
                $result[$keys]['transfer_rate'] = $transferRate.'%';
            }
        }
        if(isset($footerData['evalute1'])) {
            $footerData['evalute_rate'] = empty($footerData['evalute1'] + $footerData['evalute-3']) ? '0%' : (round($footerData['evalute1']/($footerData['evalute1'] + $footerData['evalute-3']), 4)*100).'%';
            $transferRate2 = empty($counts) ? '0' : (round(($footerData['evalute1'] + $footerData['evalute-3'])/$counts, 4)*100);
            if ($transferRate2 > 100) {
                $footerData['transfer_rate'] = '100%';
            } else {
                $footerData['transfer_rate'] = $transferRate2.'%';
            }
        }


        return array(
            'code' => 200,
            'message' => 'ok',
            'data' => array_values($result),
            'footer' => $footerData,
        );
    }

    /**
     * 短信明细数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getSmsListData(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $infos = empty($param['info']) ? '' : $this->purifyHtml($param['info']);

        /** @var array $msg 验证vcc_code是否正确 */
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }

        //分页搜索相关信息；
        $info = array();
        if (!empty($infos)) {
            $info = json_decode($infos, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        /** @var  $wintelapi_sms (短信接口地址) */
        $wintelapi_sms = $this->container->getParameter('wintelapi_sms');
        /** @var  $sms_flag (短信标记，用于获取不同平台的短信) */
        $sms_flag = $this->container->getParameter('sms_flag');
        $info['filter']['flag'] = $sms_flag;

        /** 判断是否是导出，如果是导出不分页 */
        if (empty($info['export'])) {
            $info['pagination']['page'] = empty($info['pagination']["page"]) ?
                1 : $info['pagination']["page"];
            $info['pagination']['rows'] = empty($info['pagination']["rows"]) ?
                10 : $info['pagination']["rows"];
        } else {
            $info['pagination']['page'] = 1;
            $info['pagination']['rows'] = 10000;
        }

        /** 排序条件 */
        $info['sort']['field'] = empty($info['sort']["sort"]) ? "id" : $info['sort']["sort"];
        $info['sort']['order'] = empty($info['sort']["order"]) ? "DESC" : $info['sort']["order"];

        $time = $this->container->get('icsoc_core.common.class')->rolesCanSearchAllReportDatas($info['filter']['start_time'], $info['filter']['end_time'], 'date');

        /** 查询条件 */
        if (!empty($info['filter']["start_time"])) {
            $info['filter']['start_time'] = $info['filter']["start_time"];
            if (isset($time['startTime'])) {
                $info['filter']['start_time'] = $time['startTime'];
            }
        }
        if (!empty($info['filter']["end_time"])) {
            $info['filter']['end_time'] = $info['filter']["end_time"];
            if (isset($time['endTime'])) {
                $info['filter']['end_time'] = $time['endTime'];
            }
        }
        if (!empty($info['filter']["phone"])) {
            $info['filter']['phone'] = $info['filter']["phone"];
        }
        if (isset($info['filter']["result"]) && $info['filter']["result"] != -1) {
            $info['filter']['result'] = $info['filter']["result"];
        }
        $params = array(
            'vcc_code' => $vccCode,
            'info' => json_encode($info),
        );
        if ($time == 'no') {
            $params['vcc_code'] = 0;
        }
        $client = new Client();
        $request = $client->post($wintelapi_sms.'/sms/list', array(), $params);
        $response = $request->send()->json();

        if (json_last_error() !== JSON_ERROR_NONE) {
            return array(
                'code' => 404,
                'message' => '解析结果出错，错误为【'.json_last_error().'】',
            );
        }

        $code = isset($response['code']) ? $response['code'] : 0;
        $message = isset($response['message']) ? $response['message'] : 0;
        $total = isset($response['total']) ? $response['total'] : '';
        $data = isset($response['data']) ? $response['data'] : array();

        if ($code == 200) {
            $limit = isset($info['pagination']['rows']) ? $info['pagination']['rows'] : 1000;
            $total_pages = ceil($total / $limit);

            return array(
                'code' => 200,
                'message' => 'ok',
                'total' => $total,
                'page' => $info['pagination']['page'],
                'total_pages' => $total_pages,
                'data' => $data,
            );
        } else {
            return array(
                'code' => 405,
                'message' => $message,
            );
        }
    }

    /**
     * 获取录音数据
     *
     * @param array $param
     *
     * @return array
     */
    public function getRecordListFromElasticsearch(array $param = array())
    {
        $vccCode = empty($param['vcc_code']) ? 0 : $param['vcc_code'];
        $info = empty($param['info']) ? '' : $this->purifyHtml($param['info']);
        $endResult = $this->container->getParameter("end_type");
        $msg = $vccId = $this->container->get('icsoc_data.validator')->checkVccCode($vccCode);
        $evaluates = $this->container->getParameter('EVALUATES');
        if (!empty($msg) && is_array($msg)) {
            return $msg;
        }
        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            if (!is_string($info)) {
                return array('code' => 403, 'message' => '参数格式错误');
            }
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return array('code' => 403, 'message' => 'info格式非json');
            }
        }

        $rows = empty($addInfo['pagination']['rows']) ? 10 : $addInfo['pagination']['rows'];
        $page = empty($addInfo['pagination']['page']) ? 1 : $addInfo['pagination']['page'];
        $order = empty($addInfo['sort']['order']) ? 'desc' : $addInfo['sort']['order'];
        $type = "win_agcdr";
        $condition = array(
            'fixup' => array(
                'index' => 'agcdr',
                'type' => $type,
                'rows' => $rows == -1 ? 1000 : $rows,
                'page' => $page,
                'order' => $order,
                'field' => 'id',
            ),
            'term' => array('vcc_id' => array('type' => 'match', 'value' => $vccId)),
        );

        if (isset($addInfo['filter'])) {
            //坐席工号；
            if (!empty($addInfo['filter']['ag_num']) && $addInfo['filter']['ag_num'] != '-1') {
                $agId = (int) $addInfo['filter']['ag_num'];
                $condition['term']['ag_id'] = array('type' => 'match', 'value' => $agId);
            }
            //呼叫类型
            if (isset($addInfo['filter']['call_type']) && !empty($addInfo['filter']['call_type'])) {
                $callType = (int) $addInfo['filter']['call_type'];
                if ($callType == 1) {
                    $condition['term']['call_type'] = array('type' => 'should', 'value' => '1,3,5');
                } elseif ($callType == 2) {
                    $condition['term']['call_type'] = array('type' => 'should', 'value' => '2,4,6');
                }
            }

            //坐席号码
            if (isset($addInfo['filter']['ag_phone']) && !empty($addInfo['filter']['ag_phone'])) {
                $condition['term']['ag_phone'] = array('type' => 'wildcard', 'value' => $addInfo['filter']['ag_phone']);
            }

            //客户号码
            if (isset($addInfo['filter']['cus_phone']) && !empty($addInfo['filter']['cus_phone'])) {
                $condition['term']['cus_phone'] = array('type' => 'wildcard', 'value' => $addInfo['filter']['cus_phone']);
            }

            //开始时间
            if (isset($addInfo['filter']['start_time']) && !empty($addInfo['filter']['start_time'])) {
                $startTime = $addInfo['filter']['start_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('开始时间格式不正确', $startTime, 404);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }

                $condition['term']['start_time'] = array('type' => 'range', 'value' => strtotime($startTime), 'field' => 'start_time', 'operation' => 'gte');
            }

            //结束时间
            if (isset($addInfo['filter']['end_time']) && !empty($addInfo['filter']['end_time'])) {
                $endTime = $addInfo['filter']['end_time'];
                $msg = $this->container->get('icsoc_data.helper')->isDateTime('结束时间格式不正确', $endTime, 405);
                if (!empty($msg) && is_array($msg)) {
                    return $msg;
                }

                $condition['term']['end_time'] = array('type' => 'range', 'value' => strtotime($endTime), 'field' => 'start_time', 'operation' => 'lte');
            }

            //时长小于
            if (isset($addInfo['filter']['esecs']) && !empty($addInfo['filter']['esecs'])) {
                $esecs = (int) $addInfo['filter']['esecs'];
                $condition['term']['esecs'] = array('type' => 'range', 'value' => $esecs, 'field' => 'conn_secs', 'operation' => 'lte');
            }

            //时长大于
            if (isset($addInfo['filter']['ssecs']) && !empty($addInfo['filter']['ssecs'])) {
                $ssecs = (int) $addInfo['filter']['ssecs'];
                $condition['term']['ssecs'] = array('type' => 'range', 'value' => $ssecs, 'field' => 'conn_secs', 'operation' => 'gte');
            }

            //技能组id
            if (isset($addInfo['filter']['que_id']) && $addInfo['filter']['que_id'] != '-1') {
                $queId = (int) $addInfo['filter']['que_id'];
                $condition['term']['que_id'] = array('type' => 'match', 'value' => $queId);
            }
            //结束类型
            if (isset($addInfo['filter']['end_result']) && !empty($addInfo['filter']['end_result'])) {
                $condition['term']['endresult'] = array('type' => 'match', 'value' => $addInfo['filter']['end_result']);
            }
            //评价结果
            if (isset($addInfo['filter']['evaluates'])) {
                $evaluate = (int) $addInfo['filter']['evaluates'];
                $condition['term']['evaluate'] = array('type' => 'match', 'value' => $evaluate);
            }
            if (isset($addInfo['filter']['id']) && $addInfo['filter']['id'] !== false) {
                $condition['term']['id'] = array('type' => 'should', 'value' => $addInfo['filter']['id']);
            }

            if (isset($addInfo['filter']['group_id']) && $addInfo['filter']['group_id'] != '-1') {
                $condition['term']['group_id'] = array('type' => 'match', 'value' => $addInfo['filter']['group_id']);
            }

            //中继号码
            if (isset($addInfo['filter']['serv_num']) && !empty($addInfo['filter']['serv_num'])) {
                $condition['term']['serv_num'] = array('type' => 'match', 'value' => $addInfo['filter']['serv_num']);
            }
        }

        $authority = $this->container->get("icsoc_core.common.class")->getAuthorityConditionForElasearch('ag_id', 'que_id', 1);
        if ($authority === false) {
            return array(
                'code' => 404,
                'message' => '权限内无任何数据',
            );
        } else {
            if (!empty($authority['ag_id']) && $addInfo['filter']['ag_num'] == '-1') {
                $condition['term']['ag_id'] = array('type' => 'terms', 'value' => $authority['ag_id']);
            }
            if (!empty($authority['que_id']) && $addInfo['filter']['que_id'] == '-1') {
                $condition['term']['que_id'] = array('type' => 'terms', 'value' => $authority['que_id']);
            }
            if (!empty($authority['group_id']) && $addInfo['filter']['group_id'] == '-1') {
                $condition['term']['group_id'] = array('type' => 'terms', 'value' => $authority['group_id']);
            }
        }
        $condition['term']['record_mark'] = array('type' => 'match', 'value' => 1);

        $rows = $this->getDataFromESAndMongo($condition, 'win_agcdr');
        $resultData = $rows['data'];
        $count = $rows['count'];
        $totalPages = $rows['totalPages'];

        $agNicknames = $this->dbal->fetchAll(
            "SELECT id,ag_nickname FROM win_agent WHERE vcc_id = :vcc_id ",
            array('vcc_id' => $vccId)
        );
        $nicknameInfo = array();
        foreach ($agNicknames as $k => $v) {
            $nicknameInfo[$v['id']] = $v['ag_nickname'];
        }

        $result = array();
        foreach ($resultData as $k => $v) {
            $result[$k]['id'] = (string) $v['_id'];
            $result[$k]['server_num'] = $v['serv_num'];
            $result[$k]['ag_id'] = $v['ag_id'];
            $result[$k]['agcdr_id'] = isset($v['agcdr_id']) ? (string) $v['agcdr_id'] : '';
            $result[$k]['ag_phone'] = $v['ag_phone'];
            $result[$k]['cus_phone'] = $v['cus_phone'];
            $result[$k]['que_name'] = $v['que_name'];
            $result[$k]['record_file'] = $v['record_file'];
            $result[$k]['conn_secs'] = $v['conn_secs'];
            $result[$k]['ag_num'] = $v['ag_num'];
            $result[$k]['ag_name'] = $v['ag_name'];
            $result[$k]['ag_nickname'] = !empty($nicknameInfo[$v['ag_id']]) ? $nicknameInfo[$v['ag_id']] : '';
            $result[$k]['call_id'] = (string) $v['call_id'];
            $result[$k]['group_id'] = $v['group_id'];
            $result[$k]['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : "";
            $result[$k]['end_time'] = $v['end_time'] ? date("Y-m-d H:i:s", $v['end_time']) : "";
            if ($v['call_type'] == 1 || $v['call_type'] == 3 || $v['call_type'] == 5) {
                $result[$k]['call_type'] = $this->rcalltype[1]; // out
            } else {
                $result[$k]['call_type'] = $this->rcalltype[2]; // in
            }
            $result[$k]['evaluate'] = isset($evaluates[$v['evaluate']]) ? $evaluates[$v['evaluate']] : '';
            $result[$k]['endresult'] = isset($endResult[$v['endresult']]) ?
                $endResult[$v['endresult']] : '其它';
            unset($rows[$k]);
        }
        unset($resultData);

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => $count,
            'data' => $result,
            'totalPage' => $totalPages,
            'page' => $page,
        );
    }

    /**
     * 根句条件在elasticsearch和mongodb中获取数据
     *
     * @param $condition
     * @param $mongoColumn
     * @param array $order
     * @return array
     */
    public function getDataFromESAndMongo($condition, $mongoColumn, $order = array('field' => '_id', 'order' => 'desc'))
    {
        //从elasticsearch中查询出满足条件的数据的ID
        $data = $this->searchData($condition);
        $res = $data['data'];
        $page = $data['page'];
        $count = $data['count'];
        $totalPages = $data['total'];
        $ids = array();
        foreach ($res as $k => $re) {
            $ids[] = $re['id'];
            unset($res[$k]);
        }
        $params['allIds'] = $ids;
        unset($data);
        unset($res);
        unset($ids);

        //从mongodb中，通过ID获取数据的值
        $result = $this->container->get('icsoc_core.mongodb_common.class')->getDataForPage($params, $mongoColumn);
        $rows = array();

        foreach ($result as $val) {
            $rows[] = $val;
        }
        unset($result);

        $arr = array();
        foreach ($rows as $k => $v) {
            $arr[$k] = $v[$order['field']];
        }
        if ($order['order'] == 'desc') {
            arsort($arr);
        } else {
            asort($arr);
        }
        $resultData = array();
        //根据规定的字段倒序排序
        foreach ($arr as $k => $val) {
            $resultData[] = $rows[$k];
        }
        unset($arr);
        unset($rows);
        unset($params);

        return array('page' => $page, 'count' => $count, 'totalPages' => $totalPages, 'data' => $resultData);
    }

    /**
     * 从elasticsearch中获取数据(wintelApi接口专用，返回所有数据，没有分页)
     *
     * @param array $condition
     *
     * @return array
     */
    public function searchData(array $condition)
    {
        $index = isset($condition['fixup']['index']) ? $condition['fixup']['index'] : '';
        $esType = isset($condition['fixup']['type']) ? $condition['fixup']['type'] : '';
        $field = isset($condition['fixup']['field']) ? $condition['fixup']['field'] : 'id';
        $order = isset($condition['fixup']['order']) ? $condition['fixup']['order'] : 'desc';

        $query = array(
            'query' => array(),
        );
        if (is_array($field)) {
            foreach ($field as $f) {
                $query['sort'][] = array($f => array('order' => $order, 'unmapped_type' => 'date'));
            }
        } else {
            $query['sort'] = array($field => array('order' => $order, 'unmapped_type' => 'date'));
        }

        $terms = $condition['term'];
        foreach ($terms as $k => $v) {
            switch ($v['type']) {
                case 'range':
                    $query['query']['bool']['must'][] = array('range' => array($v['field'] => array($v['operation'] => $v['value'])));
                    break;
                case 'match':
                    $query['query']['bool']['must'][] = array('match' => array($k => $v['value']));
                    break;
                case 'nomatch':
                    $query['query']['bool']['must_not'][] = array('match' => array($k => $v['value']));
                    break;
                case 'wildcard':
                    if (!isset($v['bool'])) {
                        $query['query']['bool']['must'][] = array('wildcard' => array($k => "*{$v['value']}*"));
                    } else {
                        $query['query']['bool']['must'][] = array('bool' => array($v['operation'] => array(array('wildcard' => array($k => "*{$v['value']}*")), array('match' => array($v['field'] => array('query' => $v['value'], 'operator' => 'and'))))));
                    }
                    break;
                case 'terms':
                    $query['query']['bool']['must'][] = array('terms' => array($k => $v['value']));
                    break;
                case 'should':
                    $values = explode(',', $v['value']);
                    foreach ($values as $val) {
                        $query['query']['bool']['should'][] = array('match' => array($k => $val));
                    }
                    $query['query']['bool']['minimum_should_match'] = 1;//至少匹配一个
                    break;
                case 'or':
                    if (isset($v['columns'])) {
                        foreach ($v['columns'] as $column) {
                            $query['query']['bool']['should'][] = array('wildcard' => array($column => "*{$v['value']}*"));
                        }
                    }
                    $query['query']['bool']['minimum_should_match'] = 1;//至少匹配一个
                    break;
                case 'multiNomatch':
                    if (isset($v['multi'])) {
                        foreach ($v['value'] as $value) {
                            $query['query']['bool']['must_not'][] = array('match' => array($k => $value));
                        }
                    }
                    break;
            }
        }

        $params = array(
            'index' => $index,
            'type' => $esType,
            'body' => $query,
            'search_type' => 'scan',
            'scroll' => '30s'
        );

        $sources = array();
        $total = 0;

        try {
            $hosts = $this->container->getParameter('elasticsearch_hosts');
            $client = new Client(array('hosts' => $hosts));
            $result = $client->search($params);
            $scrollId = $result['_scroll_id'];
            while (true) {
                $response = $client->scroll(
                    array(
                        "scroll_id" => $scrollId,
                        "scroll" => "30s",
                    )
                );

                if (count($response['hits']['hits']) > 0) {
                    $sources = array_merge($sources, $response['hits']['hits']);
                    $total = $response['hits']['total'];
                    $scrollId = $response['_scroll_id'];
                } else {
                    break;
                }
            }
        } catch (\Exception $e) {
            $logger = $this->container->get('logger');
            $logger->error($e->getMessage());

            return array(
                'code' => 500,
                'message' => '发生异常',
                'total' => 0,
                'page' => 0,
                'total_pages' => 0,
                'data' => array(),
            );
        }

        $data = array();
        foreach ($sources as $source) {
            $data[] = $source['_source'];
        }

        return array(
            'code' => 200,
            'message' => 'ok',
            'total' => 1,
            'count' => $total,
            'page' => 1,
            'data' => $data,
        );
    }
}
